require "rexml/document"

class KpiDal
  def initialize(review_type,num_of_reviews)
    @avg_ratings_records=get_avg_ratings_records(review_type.id)
    @percent_of_ratings_records=get_percent_of_ratings_records(
      review_type.id,
      num_of_reviews
    )
  end
  
  def get_avg_rating(rating_criterion)
    record=@avg_ratings_records.find{|record|
      record["rating_criterion_id"]==rating_criterion.id
    }
    get_value_or_default(record,"avg_rating_value",0)
  end
  
  def get_percent_of_ratings(rating_criterion,rating_scale_element)
    record=get_percent_of_ratings_record(rating_criterion,rating_scale_element)
    get_value_or_default(record,"percent_of_ratings",0.0)
  end

  def get_num_of_ratings(rating_criterion,rating_scale_element)
    record=get_percent_of_ratings_record(rating_criterion,rating_scale_element)
    get_value_or_default(record,"num_of_ratings",0)
  end

  private
  def get_value_or_default(record,column_name,default_value)
    record.nil? ? default_value : record[column_name]
  end

  def get_avg_ratings_records(review_type_id)
      return Rating.connection.select_all(
"
SELECT ratings.rating_criterion_id,avg(ratings.rating_value) as avg_rating_value
FROM 
ratings inner join
reviews on ratings.review_id=reviews.id 
WHERE reviews.review_type_id=%i
GROUP BY ratings.rating_criterion_id
" % review_type_id)
  end

  def get_percent_of_ratings_record(rating_criterion,rating_scale_element)
    return @percent_of_ratings_records.find{|record|
      record["rating_criterion_id"]==rating_criterion.id and
      record["rating_value"]==rating_scale_element.value
    }
  end

  def get_percent_of_ratings_records(review_type_id,num_of_reviews)
      return Rating.connection.select_all(
"
SELECT ratings.rating_criterion_id,ratings.rating_value,count(*) as num_of_ratings, count(*)/%i.0 as percent_of_ratings
FROM 
ratings inner join
reviews on ratings.review_id=reviews.id
WHERE reviews.review_type_id=%i
GROUP BY ratings.rating_criterion_id,ratings.rating_value
" % [num_of_reviews,review_type_id])
  end
end
